﻿CREATE PROCEDURE [dbo].[spSelectPackagesForPrinting]
	@OwnerID int,
	@LocationID int
AS
set nocount on

exec spPackageInventory @OwnerID, @LocationID

declare @ToPrint table ( AddressID int, PersonID int, InventoryTransactionID int, CarrierCode char(1), CustomerID int )
insert into @ToPrint (AddressID, PersonID, InventoryTransactionID, CarrierCode, CustomerID)
select oh.ShipTo_AddressID, oh.ShipTo_PersonID, it.InventoryTransactionID, pac.CarrierCode, oh.CustomerID
from tblPackage pac
	join tblInventoryTransaction it on pac.PackageID = it.PackageID
	join tblOrderHeader oh on it.OrderID = oh.OrderID
where it.InventoryTransactionStatusCode = 'A'
	and oh.OrderStatusCode = 'O'

-- tblPackage
-- Sort the results by the carrier and items in the package putting the largest groups first.
select T1.* 
from tblPackage T1
	join ( select T1.PackageID, checksum_agg( T3.ProductID ) as chksum
		from tblInventoryTransaction T1 
			join @ToPrint T2 on T1.InventoryTransactionID = T2.InventoryTransactionID 
			join tblInventory T3 on T1.InventoryID = T3.InventoryID
		group by T1.PackageID
	) as T2 on T1.PackageID = T2.PackageID
	order by count(T1.PackageID) over( partition by T1.CarrierCode, T2.chksum ) desc

-- tblInventoryTransaction
select T1.* from tblInventoryTransaction T1
	join @ToPrint T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
where T1.InventoryTransactionStatusCode = 'A'

-- tblInventory
select * from tblInventory
where InventoryID in ( select T1.InventoryID from tblInventoryTransaction T1 
		join @ToPrint T2 on T1.InventoryTransactionID = T2.InventoryTransactionID 
		where T1.InventoryTransactionStatusCode = 'A')

-- tblProduct
select * from tblProduct
where ProductID in ( select T1.ProductID from tblInventory T1
		join tblInventoryTransaction T2 on T1.InventoryID = T2.InventoryID
		join @ToPrint T3 on T2.InventoryTransactionID = T3.InventoryTransactionID 
		where T2.InventoryTransactionStatusCode = 'A')

--tblPerson
select * from tblPerson
where PersonID in ( select PersonID from @ToPrint )

--tblAddress
select * from tblAddress
where AddressID in ( select AddressID from @ToPrint )
